<?php

class SalesreportController extends Controller
{

    public function filters()
    {
        return array(
            'accessControl', // perform access control for CRUD operations
            array(
                'application.filters.RequireLoginFilter',
            ),
        );
    }

    

    public function actionInvoiceReport()
    {

        $model = new InvoiceReportForm();
        $showTemplate = false;
        if (isset($_POST['InvoiceReportForm']))
        {
            $model->setAttributes($_POST['InvoiceReportForm']);
            if ($model->validate())
            {
                $showTemplate = true;

                $this->layout = '//layout/space';
                $this->render('invoiceReportResult', array(
                    'model' => $model,
                    'invoiceList' => $model->getData()
                ));
            }
        }
        if (!$showTemplate)
        {
            $this->render('invoiceReport', array(
                'model' => $model
            ));
        }
    }

    public function actionSalesOrderReport()
    {
        $model = new SalesOrderReportForm;
        $showResult = false;
        if (isset($_POST['SalesOrderReportForm']))
        {
            $model->setAttributes($_POST['SalesOrderReportForm']);
            if ($model->validate())
            {
                $showResult = true;
                $this->layout = '//layout/space';
                $this->render('salesOrderReportResult', array(
                    'model' => $model,
                ));
            }
        }
        if (!$showResult)
        {
            $this->render('salesOrderReport', array(
                'model' => $model
            ));
        }
    }

    public function actionSalesReport()
    {
        //get all invoice in the week
        //check if it isnt paid in full
        //get all sales order in week
        //check if it isn't convert to invoice

        $this->layout = '//layout/space';
        $totalInvoice = Yii::app()->db->createCommand(
                        'select sum(amountcharge) from tbl_invoice i
                 left join tbl_rec r on r.invoice= i.invoice
                 where r.paidoff is null and i.invoicetype=:type
            ')->bindValues(array('type' => Invoice::TYPE_INVOICE))->queryScalar();
        $invoiceList=Yii::app()->db->createCommand(
                        'select * from tbl_invoice i
                 left join tbl_rec r on r.invoice= i.invoice
                 where r.paidoff is null and i.invoicetype=:type
            ')->bindValues(array('type' => Invoice::TYPE_INVOICE))->queryAll();
        $invoiceTotalByDepartment=array();
        foreach(SalesDepartments::getListData() as $key=>$value){
            $invoiceTotalByDepartment[$key]=0;
        }
        unset($invoiceTotalByDepartment['']);

        foreach($invoiceList as $invoice){
            if($invoice['department']!=null)
                $invoiceTotalByDepartment[$invoice['department']]+=$invoice['amountcharge'];
        }
        
        $salesOrderList = Yii::app()->db->createCommand('select * from tbl_invoice where invoicetype=:type')
                ->bindValue('type', Invoice::TYPE_SALES_ORDERS)
                ->queryAll();
        $totalSalesOrder = 0;
        $salesOrderInvoiceList = array();
        $modifiedSalesOrderList=array();
        foreach ($salesOrderList as $salesOrder)
        {

            $invoiceList = Yii::app()->db->createCommand('select * from tbl_invoice where invoicetype=:type AND salesordersorg=:number')
                            ->bindValues(array(
                                'type' => Invoice::TYPE_INVOICE,
                                'number' => $salesOrder['invoice']
                            ))->queryAll();
            $salesOrderInvoiceList[$salesOrder['invoice']] = $invoiceList;
            $subtotal = 0;
            foreach ($invoiceList as $invoice)
            {
                $subtotal+=$invoice['amountcharge'];
            }
            if ($salesOrder['amountcharge'] - $subtotal > 0)
            {
                $totalSalesOrder+=$salesOrder['amountcharge'] - $subtotal;
                $salesOrder['unpaidamount']=$salesOrder['amountcharge'] - $subtotal;
                $modifiedSalesOrderList[]=$salesOrder;
            }
        }
        $salesOrderListByType = array();
        $salesOrderDeptList=array();
        $statusList = SalesDepartments::getListData();
        $statusList = array_slice($statusList, 1);
        foreach ($statusList as $status)
        {
            $salesOrderListByType[$status] = 0;
            $salesOrderDeptList[$status]=array();
        }
        foreach ($modifiedSalesOrderList as $salesOrder)
        {
            $status = SalesDepartments::getText($salesOrder['department']);
            if ($status != null)
            {            
                $salesOrderListByType[$status]+=$salesOrder['unpaidamount'];
                $salesOrderDeptList[$status][]=$salesOrder;
            }
        }


        $totalUninvoiceContract = Yii::app()->db->createCommand('select sum(c.contractamount)
                                                from tbl_contracts c
                                                where c.contractid not in 
                                                (select agreementno from tbl_invoice 
                                               )
	')->queryScalar();
        $uninvoiceContractList = Yii::app()->db->createCommand('select *
                                                from tbl_contracts c
                                                where c.contractid not in 
                                                (select agreementno from tbl_invoice 
                                             )
	')->queryAll();

        $uninvoiceContractListByType = array();
        foreach(ContractType::getListData() as $key=>$value){
            $uninvoiceContractListByType[$key]['total'] = 0;
            $uninvoiceContractListByType[$key]['list']=array();
        }
        foreach ($uninvoiceContractList as $contract)
        {       
            $uninvoiceContractListByType[$contract['contractype']]['total']+=$contract['contractamount'];
            $uninvoiceContractListByType[$contract['contractype']]['list'][] = $contract;
        }
        $this->render('salesreport', array(
            'totalInvoice' => $totalInvoice,
            'totalSalesOrder' => $totalSalesOrder,
            'salesOrderInvoiceList' => $salesOrderInvoiceList,
            'salesOrderList' => $salesOrderList,
            'totalUninvoiceContract' => $totalUninvoiceContract,
            'uninvoiceContractList' => $uninvoiceContractList,
            'uninvoiceContractListByType' => $uninvoiceContractListByType,
            'salesOrderListByType' => $salesOrderListByType,
            'invoiceTotalByDepartment'=>$invoiceTotalByDepartment,
            'salesOrderDeptList'=>$salesOrderDeptList
        ));
    }
	
	public function actionSalesReportTest()
    {     
        $this->layout = '//layout/space';
        $totalInvoice = Yii::app()->db->createCommand(
                        ' SELECT SUM(amountcharge)
                    FROM
                     tbl_invoice i LEFT JOIN (SELECT * FROM tbl_rec r
                                      WHERE r.paidoff is NULL                      
                    ) as k ON i.invoice = k.invoice
                    WHERE i.invoicetype =:type
            ')->bindValues(array('type' => Invoice::TYPE_INVOICE))->queryScalar();      
        ini_set('memory_limit', '-1');
         $invoiceList=Yii::app()->db->createCommand(
                        'SELECT *
                    FROM
                     tbl_invoice i LEFT JOIN (SELECT * FROM tbl_rec r
                                      WHERE r.paidoff is NULL                      
                    ) as k ON i.invoice = k.invoice
                    WHERE i.invoicetype =:type
            ')->bindValues(array('type' => Invoice::TYPE_INVOICE))->queryAll();
    
        $invoiceTotalByDepartment=array();        
        foreach(SalesDepartments::getListData() as $key=>$value){
            $invoiceTotalByDepartment[$key]=0;
        }
        unset($invoiceTotalByDepartment['']);       

        foreach($invoiceList as $invoice){
            if($invoice['department']!=null)
                $invoiceTotalByDepartment[$invoice['department']]+=$invoice['amountcharge'];
        }
        
        $salesOrderList = Yii::app()->db->createCommand('select * from tbl_invoice where invoicetype=:type')
                ->bindValue('type', Invoice::TYPE_SALES_ORDERS)
                ->queryAll();
        $totalSalesOrder = 0;           
        $salesOrderInvoiceList = array();
        $modifiedSalesOrderList=array();
        $saleOrderStats = Yii::app()->db->createCommand(
                        'SELECT * FROM tbl_invoice inv LEFT JOIN
  (SELECT 
  s.invoice as sale_invoice,(SUM(i.amountcharge))  as subtotal,s.amountcharge as sale_amountcharge,s.department as sale_department
  FROM
    tbl_invoice i, (select invoice,amountcharge,department from tbl_invoice where invoicetype=:type_sale) as s
  WHERE
    i.invoicetype=:type_inv  AND i.salesordersorg=s.invoice
GROUP
 BY s.invoice) as sale_view ON
  sale_view.sale_invoice = inv.salesordersorg
  WHERE
invoicetype=:type_inv  AND
salesordersorg<>"" AND salesordersorg IS NOT NULL AND sale_view.sale_invoice IS NOT NULL
  ORDER BY
   sale_view.sale_invoice')->bindValues(array('type_inv' => Invoice::TYPE_INVOICE,
                'type_sale'=> Invoice::TYPE_SALES_ORDERS,
                ))->queryAll();   
        $handlingData = $this->makeListDataForWeeklyReport($saleOrderStats);
        foreach ($salesOrderList as $salesOrder)
        {
            if(!isset($handlingData[$salesOrder['invoice']]))
            {
                 $salesOrderInvoiceList[$salesOrder['invoice']] = array();
                 continue;
            }
            $dataItem = $handlingData[$salesOrder['invoice']];
            $salesOrderInvoiceList[$salesOrder['invoice']] = $dataItem['InvoiceList'];
            if ($salesOrder['amountcharge'] - $dataItem['subtotal'] > 0)
            {
                $totalSalesOrder+=$salesOrder['amountcharge']  -  $dataItem['subtotal'];
                $salesOrder['unpaidamount']=$salesOrder['amountcharge'] - $dataItem['subtotal'];
                $modifiedSalesOrderList[]=$salesOrder;
            }   
        }
        $salesOrderListByType = array();
        $salesOrderDeptList=array();
        $statusList = SalesDepartments::getListData();
        $statusList = array_slice($statusList, 1);
        foreach ($statusList as $status)
        {
            $salesOrderListByType[$status] = 0;
            $salesOrderDeptList[$status]=array();
        }
        foreach ($modifiedSalesOrderList as $salesOrder)
        {
            $status = SalesDepartments::getText($salesOrder['department']);
            if ($status != null)
            {            
                $salesOrderListByType[$status]+=$salesOrder['unpaidamount'];
                $salesOrderDeptList[$status][]=$salesOrder;
            }
        }
        $totalUninvoiceContract = Yii::app()->db->createCommand('select sum(c.contractamount)
                                                from tbl_contracts c
                                                where c.contractid not in 
                                                (select agreementno from tbl_invoice 
                                               )
	')->queryScalar();
        $uninvoiceContractList = Yii::app()->db->createCommand('select *
                                                from tbl_contracts c
                                                where c.contractid not in 
                                                (select agreementno from tbl_invoice 
                                             )
	')->queryAll();

        $uninvoiceContractListByType = array();
        foreach(ContractType::getListData() as $key=>$value){
            $uninvoiceContractListByType[$key]['total'] = 0;
            $uninvoiceContractListByType[$key]['list']=array();
        }
        foreach ($uninvoiceContractList as $contract)
        {       
            $uninvoiceContractListByType[$contract['contractype']]['total']+=$contract['contractamount'];
            $uninvoiceContractListByType[$contract['contractype']]['list'][] = $contract;
        }       
        $this->render('salesreport', array(
            'totalInvoice' => $totalInvoice,
            'totalSalesOrder' => $totalSalesOrder,
            'salesOrderInvoiceList' => $salesOrderInvoiceList,
            'salesOrderList' => $salesOrderList,
            'totalUninvoiceContract' => $totalUninvoiceContract,
            'uninvoiceContractList' => $uninvoiceContractList,
            'uninvoiceContractListByType' => $uninvoiceContractListByType,
            'salesOrderListByType' => $salesOrderListByType,
            'invoiceTotalByDepartment'=>$invoiceTotalByDepartment,
            'salesOrderDeptList'=>$salesOrderDeptList
        ));
    }
    protected function makeListDataForWeeklyReport($saleOrderStats){
        $data=array();
        foreach ($saleOrderStats as $stat) {
            
                if(!isset($data[$stat['sale_invoice']]))
                {
                    $data[$stat['sale_invoice']]['InvoiceList']  = array();
                }
                $data[$stat['sale_invoice']]['InvoiceList'][] = $stat;                 
                $data[$stat['sale_invoice']]['subtotal'] = $stat['subtotal'];
        }
       return $data;
    }
	
    public function actionContractReport()
    {
        $model = new ContractReportForm;
        $showResult = false;
        if (isset($_POST['ContractReportForm']))
        {
            $model->setAttributes($_POST['ContractReportForm']);
            if ($model->validate())
            {
                $showResult = true;
                $this->layout = '//layout/space';
                $this->render('contractReportResult', array(
                    'model' => $model,
                ));
            }
        }
        if (!$showResult)
        {
            $this->render('contractReport', array(
                'model' => $model
            ));
        }
    }
    

}

?>
